DROP DATABASE IF EXISTS media_catalogue;
CREATE DATABASE media_catalogue
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
USE media_catalogue;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;



CREATE TABLE genres (
    genre_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255),
    UNIQUE KEY uq_genre_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    bio TEXT,
    INDEX idx_authors_last_name (last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE directors (
    director_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    bio TEXT,
    INDEX idx_directors_last_name (last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    isbn VARCHAR(20),
    publication_year INT,
    synopsis TEXT,
    cover_image_url VARCHAR(500),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_books_isbn (isbn),
    INDEX idx_books_title (title),
    INDEX idx_books_pub_year (publication_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE movies (
    movie_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    release_year INT,
    duration_minutes INT,
    description TEXT,
    poster_url VARCHAR(500),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_movies_title (title),
    INDEX idx_movies_release_year (release_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(100),
    is_admin TINYINT(1) NOT NULL DEFAULT 0,
    gdpr_consent TINYINT(1) NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_users_email (email),
    INDEX idx_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE reviews (
    review_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    book_id INT NULL,
    movie_id INT NULL,
    rating TINYINT,
    review_text TEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(book_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (movie_id) REFERENCES movies(movie_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX idx_reviews_user (user_id),
    INDEX idx_reviews_book (book_id),
    INDEX idx_reviews_movie (movie_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE wishlists (
    wishlist_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL DEFAULT 'Main Wishlist',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX idx_wishlists_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE wishlist_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    wishlist_id INT NOT NULL,
    book_id INT NULL,
    movie_id INT NULL,
    added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_wishlist_items (wishlist_id, book_id, movie_id),
    FOREIGN KEY (wishlist_id) REFERENCES wishlists(wishlist_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(book_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (movie_id) REFERENCES movies(movie_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE book_authors (
    book_id INT NOT NULL,
    author_id INT NOT NULL,
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE movie_directors (
    movie_id INT NOT NULL,
    director_id INT NOT NULL,
    PRIMARY KEY (movie_id, director_id),
    FOREIGN KEY (movie_id) REFERENCES movies(movie_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (director_id) REFERENCES directors(director_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE book_genres (
    book_id INT NOT NULL,
    genre_id INT NOT NULL,
    PRIMARY KEY (book_id, genre_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE movie_genres (
    movie_id INT NOT NULL,
    genre_id INT NOT NULL,
    PRIMARY KEY (movie_id, genre_id),
    FOREIGN KEY (movie_id) REFERENCES movies(movie_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;



DELIMITER $$

CREATE TRIGGER trg_users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.email = LOWER(NEW.email);
END$$

CREATE TRIGGER trg_reviews_before_insert
BEFORE INSERT ON reviews
FOR EACH ROW
BEGIN
    IF NEW.rating IS NOT NULL AND (NEW.rating < 1 OR NEW.rating > 5) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Rating must be between 1 and 5';
    END IF;
END$$

CREATE TRIGGER trg_reviews_before_update
BEFORE UPDATE ON reviews
FOR EACH ROW
BEGIN
    IF NEW.rating IS NOT NULL AND (NEW.rating < 1 OR NEW.rating > 5) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Rating must be between 1 and 5';
    END IF;
END$$

CREATE TRIGGER trg_wishlist_items_before_insert
BEFORE INSERT ON wishlist_items
FOR EACH ROW
BEGIN
    IF NEW.book_id IS NULL AND NEW.movie_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Wishlist item must reference either a book or a movie';
    END IF;
END$$

DELIMITER ;



DELIMITER $$

CREATE PROCEDURE sp_add_book_with_author (
    IN p_title        VARCHAR(255),
    IN p_author_first VARCHAR(100),
    IN p_author_last  VARCHAR(100)
)
BEGIN
    DECLARE v_book_id INT;
    DECLARE v_author_id INT;

    INSERT INTO books (title) VALUES (p_title);
    SET v_book_id = LAST_INSERT_ID();

    SELECT author_id INTO v_author_id
    FROM authors
    WHERE first_name = p_author_first AND last_name = p_author_last
    LIMIT 1;

    IF v_author_id IS NULL THEN
        INSERT INTO authors (first_name, last_name)
        VALUES (p_author_first, p_author_last);
        SET v_author_id = LAST_INSERT_ID();
    END IF;

    INSERT IGNORE INTO book_authors (book_id, author_id)
    VALUES (v_book_id, v_author_id);
END$$

DELIMITER ;



INSERT INTO genres (name, description) VALUES
('Fantasy', 'Fantasy and speculative fiction'),
('Science Fiction', 'Sci-Fi and futuristic themes'),
('Mystery', 'Detective and mystery stories'),
('Drama', 'Dramatic narratives'),
('Action', 'High-paced action content');

INSERT INTO users (email, password_hash, display_name, is_admin, gdpr_consent, active) VALUES
('admin@example.com', '$2y$10$examplehashadmin', 'Admin User', 1, 1, 1),
('alice@example.com', '$2y$10$examplehashuser1', 'Alice', 0, 1, 1),
('bob@example.com', '$2y$10$examplehashuser2', 'Bob', 0, 1, 1),
('john.smith@example.com', 'hashed_password_john123', 'John Smith', 1, 1, 1),
('emma.johnson@example.com', 'hashed_password_emma456', 'Emma Johnson', 0, 1, 1),
('michael.brown@example.com', 'hashed_password_michael789', 'Michael Brown', 0, 1, 1);

INSERT INTO authors (first_name, last_name, bio) VALUES
('J.K.', 'Rowling', 'British author best known for Harry Potter.'),
('Isaac', 'Asimov', 'Prolific science fiction writer.');

INSERT INTO directors (first_name, last_name, bio) VALUES
('Christopher', 'Nolan', 'British-American film director.'),
('Steven', 'Spielberg', 'American film director.');

INSERT INTO books (title, isbn, publication_year, synopsis) VALUES
('Harry Potter and the Philosopher''s Stone', '9780747532699', 1997, 'First book in the series.'),
('Foundation', '9780008117498', 1951, 'Classic science fiction novel.');

INSERT INTO movies (title, release_year, duration_minutes, description) VALUES
('Inception', 2010, 148, 'A thief who steals corporate secrets using dream-sharing tech.'),
('Jurassic Park', 1993, 127, 'Dinosaurs are brought back to life for a theme park.');

INSERT INTO book_authors (book_id, author_id) VALUES (1, 1), (2, 2);

INSERT INTO movie_directors (movie_id, director_id) VALUES (1, 1), (2, 2);

INSERT INTO book_genres (book_id, genre_id) VALUES (1, 1), (2, 2);

INSERT INTO movie_genres (movie_id, genre_id) VALUES (1, 2), (1, 5), (2, 5);

INSERT INTO wishlists (user_id, name) VALUES
(2, 'Alice''s Wishlist'),
(3, 'Bob''s Wishlist'),
(5, 'Emma''s Wishlist'),
(6, 'Michael''s Wishlist');

INSERT INTO wishlist_items (wishlist_id, book_id, movie_id) VALUES
(1, 1, NULL),
(1, NULL, 1),
(2, 2, NULL);

INSERT INTO reviews (user_id, book_id, movie_id, rating, review_text) VALUES
(2, 1, NULL, 5, 'Amazing book, highly recommend.'),
(3, NULL, 1, 4, 'Great movie with complex plot.');


SELECT 'media_catalogue setup complete' AS status;
